﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using DTO;
namespace DAO
{
    public class DAO_DeTai
    {
        public static List<DTO_DeTai> selectDeTai_ALL()
        {
            List<DTO_DeTai> Listdetai = new List<DTO_DeTai>();
            try
            {
                string s = "Proc_selectDeTai_ALL";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = ketnoi.Cmb();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = s;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DTO_DeTai detai = new DTO_DeTai();
                    detai.MaDT = dt.Rows[i]["MaDT"].ToString();
                    detai.TenDT = dt.Rows[i]["TenDT"].ToString();
                    detai.SVToiDa = int.Parse(dt.Rows[i]["SVToiDa"].ToString());
                    detai.SoSVHH = int.Parse(dt.Rows[i]["SoSVHH"].ToString());
                    detai.MaLoaiDT = dt.Rows[i]["MaLoaiDT"].ToString();
                    detai.MaCN = dt.Rows[i]["MaCN"].ToString();
                    detai.MaGV = dt.Rows[i]["MaGV"].ToString();
                    detai.NgayCapNhat = dt.Rows[i]["NgayCapNhat"].ToString();
                    detai.SoTuan = dt.Rows[i]["SoTuan"].ToString();
                    detai.HocKy = dt.Rows[i]["HocKy"].ToString();
                    detai.NamHoc = dt.Rows[i]["NamHoc"].ToString();
                    if ((bool)dt.Rows[i]["TrangThai"] == true)
                        detai.TrangThai = true;
                    else
                        detai.TrangThai = false;

                    Listdetai.Add(detai);
                }
                ketnoi.Cmb().Close();
                return Listdetai;
            }
            catch (Exception ex)
            {
                return Listdetai;
            }
        }
        public static List<DTO_DeTai> selectDeTai_ALL_ChonDT()
        {
            List<DTO_DeTai> Listdetai = new List<DTO_DeTai>();
            try
            {
                string s = "Proc_selectDeTai_ChonDT";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = ketnoi.Cmb();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = s;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DTO_DeTai detai = new DTO_DeTai();
                    detai.ID = int.Parse(dt.Rows[i]["ID"].ToString());
                    detai.MaDT = dt.Rows[i]["MaDT"].ToString();
                    detai.TenDT = dt.Rows[i]["TenDT"].ToString();
                    detai.SVToiDa = int.Parse(dt.Rows[i]["SVToiDa"].ToString());
                    detai.SoSVHH = int.Parse(dt.Rows[i]["SoSVHH"].ToString());
                    detai.TenloaiDT = dt.Rows[i]["TenloaiDT"].ToString();
                    detai.TenCN = dt.Rows[i]["TenCN"].ToString();
                    detai.MaLoaiDT = dt.Rows[i]["MaLoaiDT"].ToString();
                    detai.TenGV = dt.Rows[i]["TenGV"].ToString();
                    detai.NgayCapNhat = dt.Rows[i]["NgayCapNhat"].ToString();
                    detai.SoTuan = dt.Rows[i]["SoTuan"].ToString();
                    detai.HocKy = dt.Rows[i]["HocKy"].ToString();
                    detai.NamHoc = dt.Rows[i]["NamHoc"].ToString();
                    Listdetai.Add(detai);
                }
                ketnoi.Cmb().Close();
                return Listdetai;
            }
            catch (Exception ex)
            {
                return Listdetai;
            }
        }
        public static List<DTO_DeTai> ChonDT_TheoLoaiDT(string maloaidt)
        {
            List<DTO_DeTai> listdt = new List<DTO_DeTai>();
            try
            {
                string s = "Proc_selectLoaiDT_ChonDT";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = ketnoi.Cmb();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = s;
                cmd.Parameters.Add("@MaLoaiDT", SqlDbType.NVarChar).Value = maloaidt;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DTO_DeTai detai = new DTO_DeTai();

                    detai.ID = int.Parse(dt.Rows[i]["ID"].ToString());
                    detai.MaDT = dt.Rows[i]["MaDT"].ToString();
                    detai.TenDT = dt.Rows[i]["TenDT"].ToString();
                    detai.SVToiDa = int.Parse(dt.Rows[i]["SVToiDa"].ToString());
                    detai.SoSVHH = int.Parse(dt.Rows[i]["SoSVHH"].ToString());
                    detai.TenloaiDT = dt.Rows[i]["TenloaiDT"].ToString();
                    detai.TenCN = dt.Rows[i]["TenCN"].ToString();
                    detai.TenGV = dt.Rows[i]["TenGV"].ToString();
                    detai.MaLoaiDT = dt.Rows[i]["MaLoaiDT"].ToString();
                    detai.NgayCapNhat = dt.Rows[i]["NgayCapNhat"].ToString();
                    detai.SoTuan = dt.Rows[i]["SoTuan"].ToString();
                    detai.HocKy = dt.Rows[i]["HocKy"].ToString();
                    detai.NamHoc = dt.Rows[i]["NamHoc"].ToString();

                    listdt.Add(detai);
                }
                ketnoi.Cmb().Close();
                return listdt;
            }
            catch (Exception ex)
            {
                return listdt;
            }
        }
        public static List<DTO_DeTai> selectDeTai_TheoGiangVien(string MaGV)
        {
            List<DTO_DeTai> Listdetai = new List<DTO_DeTai>();
            try
            {
                string s = "Proc_selectDeTai_TheoGiangVien";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = ketnoi.Cmb();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = s;
                cmd.Parameters.Add("@MaGV", SqlDbType.NVarChar).Value = MaGV;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DTO_DeTai detai = new DTO_DeTai();
                    detai.MaDT = dt.Rows[i]["MaDT"].ToString();
                    detai.TenDT = dt.Rows[i]["TenDT"].ToString();
                    detai.SVToiDa = int.Parse(dt.Rows[i]["SVToiDa"].ToString());
                    detai.SoSVHH = int.Parse(dt.Rows[i]["SoSVHH"].ToString());
                    detai.MaLoaiDT = dt.Rows[i]["MaLoaiDT"].ToString();
                    detai.MaCN = dt.Rows[i]["MaCN"].ToString();
                    detai.NgayCapNhat = dt.Rows[i]["NgayCapNhat"].ToString();
                    detai.SoTuan = dt.Rows[i]["SoTuan"].ToString();
                    detai.HocKy = dt.Rows[i]["HocKy"].ToString();
                    detai.NamHoc = dt.Rows[i]["NamHoc"].ToString();
                    if ((bool)dt.Rows[i]["TrangThai"] == true)
                        detai.TrangThai = true;
                    else
                        detai.TrangThai = false;

                    Listdetai.Add(detai);
                }
                ketnoi.Cmb().Close();
                return Listdetai;
            }
            catch (Exception ex)
            {
                return Listdetai;
            }
        }
        public bool kiemtraDetai_giangvien(string magv)
        {
            if (selectDeTai_TheoGiangVien(magv).Count < 1)
                return false;
            return true;
        }
        public static List<DTO_DeTai> selectDeTai_TrangThai(bool trangthai)
        {
            List<DTO_DeTai> Listdetai = new List<DTO_DeTai>();
            try
            {
                string s = "Proc_SelectDeTai_TrangThai";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = ketnoi.Cmb();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = s;
                cmd.Parameters.Add("@TrangThai", SqlDbType.NVarChar).Value = trangthai;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DTO_DeTai detai = new DTO_DeTai();
                    detai.MaDT = dt.Rows[i]["MaDT"].ToString();
                    detai.TenDT = dt.Rows[i]["TenDT"].ToString();
                    detai.SVToiDa = int.Parse(dt.Rows[i]["SVToiDa"].ToString());
                    detai.SoSVHH = int.Parse(dt.Rows[i]["SoSVHH"].ToString());
                    detai.MaLoaiDT = dt.Rows[i]["MaLoaiDT"].ToString();
                    detai.MaCN = dt.Rows[i]["MaCN"].ToString();
                    detai.MaGV = dt.Rows[i]["MaGV"].ToString();
                    detai.NgayCapNhat = dt.Rows[i]["NgayCapNhat"].ToString();
                    detai.SoTuan = dt.Rows[i]["SoTuan"].ToString();
                    detai.HocKy = dt.Rows[i]["HocKy"].ToString();
                    detai.NamHoc = dt.Rows[i]["NamHoc"].ToString();
                    if ((bool)dt.Rows[i]["TrangThai"] == true)
                        detai.TrangThai = true;
                    else
                        detai.TrangThai = false;

                    Listdetai.Add(detai);
                }
                ketnoi.Cmb().Close();
                return Listdetai;
            }
            catch (Exception ex)
            {
                return Listdetai;
            }
        }
        public static List<DTO_DeTai> selectDeTai_TheoMaDT(string madt)
        {
            List<DTO_DeTai> Listdetai = new List<DTO_DeTai>();
            try
            {
                string s = "Proc_Select_TheoMaDT";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = ketnoi.Cmb();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = s;
                cmd.Parameters.Add("@MaDT", SqlDbType.NVarChar).Value = madt;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DTO_DeTai detai = new DTO_DeTai();
                    detai.MaDT = dt.Rows[i]["MaDT"].ToString();
                    detai.TenDT = dt.Rows[i]["TenDT"].ToString();
                    detai.SVToiDa = int.Parse(dt.Rows[i]["SVToiDa"].ToString());
                    detai.SoSVHH = int.Parse(dt.Rows[i]["SoSVHH"].ToString());
                    detai.MaLoaiDT = dt.Rows[i]["MaLoaiDT"].ToString();
                    detai.MaCN = dt.Rows[i]["MaCN"].ToString();
                    detai.MaGV = dt.Rows[i]["MaGV"].ToString();
                    detai.NgayCapNhat = dt.Rows[i]["NgayCapNhat"].ToString();
                    detai.SoTuan = dt.Rows[i]["SoTuan"].ToString();
                    detai.HocKy = dt.Rows[i]["HocKy"].ToString();
                    detai.NamHoc = dt.Rows[i]["NamHoc"].ToString();
                    if ((bool)dt.Rows[i]["TrangThai"] == true)
                        detai.TrangThai = true;
                    else
                        detai.TrangThai = false;

                    Listdetai.Add(detai);
                }
                ketnoi.Cmb().Close();
                return Listdetai;
            }
            catch (Exception ex)
            {
                return Listdetai;
            }
        }
        public static bool insertDeTai(DTO_DeTai detai)
        {
            try
            {
                SqlConnection cn = ketnoi.Cmb();
                string s = "Proc_InsertDetai";
                SqlCommand cmd1 = new SqlCommand(s, cn);
                cmd1.CommandType = CommandType.StoredProcedure;
                cmd1.Parameters.AddWithValue("@MaDT", detai.MaDT);
                cmd1.Parameters.AddWithValue("@TenDT", detai.TenDT);
                cmd1.Parameters.AddWithValue("@SVToiDa", detai.SVToiDa);
                cmd1.Parameters.AddWithValue("@SoSVHH", detai.SoSVHH);
                cmd1.Parameters.AddWithValue("@MaLoaiDT", detai.MaLoaiDT);
                cmd1.Parameters.AddWithValue("@MaCN", detai.MaCN);
                cmd1.Parameters.AddWithValue("@MaGV", detai.MaGV);
                cmd1.Parameters.AddWithValue("@NgayCapNhat", detai.NgayCapNhat);
                cmd1.Parameters.AddWithValue("@SoTuan", detai.SoTuan);
                cmd1.Parameters.AddWithValue("@HocKy", detai.HocKy);
                cmd1.Parameters.AddWithValue("@NamHoc", detai.NamHoc);
                cmd1.Parameters.AddWithValue("@TrangThai", detai.TrangThai);
                if (cn.State == ConnectionState.Closed)
                    cn.Open();
                cmd1.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }
        public static bool UpdateDeTai(DTO_DeTai detai)
        {
            try
            {
                SqlConnection cn = ketnoi.Cmb();
                string s = "Proc_UpdateDetai";
                SqlCommand cmd1 = new SqlCommand(s, cn);
                cmd1.CommandType = CommandType.StoredProcedure;
                cmd1.Parameters.AddWithValue("@MaDT", detai.MaDT);
                cmd1.Parameters.AddWithValue("@TenDT", detai.TenDT);
                cmd1.Parameters.AddWithValue("@SVToiDa", detai.SVToiDa);
                cmd1.Parameters.AddWithValue("@SoSVHH", detai.SoSVHH);
                cmd1.Parameters.AddWithValue("@MaLoaiDT", detai.MaLoaiDT);
                cmd1.Parameters.AddWithValue("@MaCN", detai.MaCN);
                cmd1.Parameters.AddWithValue("@MaGV", detai.MaGV);
                cmd1.Parameters.AddWithValue("@NgayCapNhat", detai.NgayCapNhat);
                cmd1.Parameters.AddWithValue("@SoTuan", detai.SoTuan);
                cmd1.Parameters.AddWithValue("@HocKy", detai.HocKy);
                cmd1.Parameters.AddWithValue("@NamHoc", detai.NamHoc);
                cmd1.Parameters.AddWithValue("@TrangThai", detai.TrangThai);
                if (cn.State == ConnectionState.Closed)
                    cn.Open();
                cmd1.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }
        public static bool DeleteDeTai(string madt)
        {
            try
            {
                SqlConnection cn = ketnoi.Cmb();
                string s = "Proc_DeleteDeTai";
                SqlCommand cmd1 = new SqlCommand(s, cn);
                cmd1.CommandType = CommandType.StoredProcedure;
                cmd1.Parameters.AddWithValue("@MaDT", madt);
                if (cn.State == ConnectionState.Closed)
                    cn.Open();
                cmd1.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }
        public static bool KiemtraTrangThaiDetai(string Madt)
        {
            List<DTO_DeTai> Listdetai = new List<DTO_DeTai>();
            try
            {
                string s = "Proc_KiemtratrangthaiDetaiDaDuyet";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = ketnoi.Cmb();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = s;
                cmd.Parameters.Add("@MaDT", SqlDbType.NVarChar).Value = Madt;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                if (dt.Rows.Count > 0)
                    return true;
                return false;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        public static bool UpdateTrangThaiDeTai(string Madt,bool trangthai)
        {
            List<DTO_DeTai> Listdetai = new List<DTO_DeTai>();
            try
            {
                SqlConnection cn = ketnoi.Cmb();
                string s = "Proc_Update_TrangThai_Detai";
                SqlCommand cmd1 = new SqlCommand(s, cn);
                cmd1.CommandType = CommandType.StoredProcedure;
                cmd1.Parameters.Add("@MaDT", SqlDbType.NVarChar).Value = Madt;
                cmd1.Parameters.Add("@trangthai", SqlDbType.NVarChar).Value = trangthai;
                if (cn.State == ConnectionState.Closed)
                    cn.Open();
                cmd1.ExecuteNonQuery();

                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        public static bool XoamemDeTai(string Madt)
        {
            List<DTO_DeTai> Listdetai = new List<DTO_DeTai>();
            try
            {
                SqlConnection cn = ketnoi.Cmb();
                string s = "Proc_XoaMenDetai";
                SqlCommand cmd1 = new SqlCommand(s, cn);
                cmd1.CommandType = CommandType.StoredProcedure;
                cmd1.Parameters.Add("@MaDT", SqlDbType.NVarChar).Value = Madt;
                if (cn.State == ConnectionState.Closed)
                    cn.Open();
                cmd1.ExecuteNonQuery();

                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        public static List<DTO_DeTai> selectDeTai_TheoMaLoaiDT(string maloaidt)
        {
            List<DTO_DeTai> Listdetai = new List<DTO_DeTai>();
            try
            {
                string s = "proc_SelectDetai_TheoLoaiDT";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = ketnoi.Cmb();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = s;
                cmd.Parameters.Add("@MaLoaiDT", SqlDbType.NVarChar).Value = maloaidt;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DTO_DeTai detai = new DTO_DeTai();
                    detai.MaDT = dt.Rows[i]["MaDT"].ToString();
                    detai.TenDT = dt.Rows[i]["TenDT"].ToString();
                    detai.SVToiDa = int.Parse(dt.Rows[i]["SVToiDa"].ToString());
                    detai.SoSVHH = int.Parse(dt.Rows[i]["SoSVHH"].ToString());
                    detai.MaLoaiDT = dt.Rows[i]["MaLoaiDT"].ToString();
                    detai.MaCN = dt.Rows[i]["MaCN"].ToString();
                    detai.NgayCapNhat = dt.Rows[i]["NgayCapNhat"].ToString();
                    detai.SoTuan = dt.Rows[i]["SoTuan"].ToString();
                    detai.HocKy = dt.Rows[i]["HocKy"].ToString();
                    detai.NamHoc = dt.Rows[i]["NamHoc"].ToString();
                    if ((bool)dt.Rows[i]["TrangThai"] == true)
                        detai.TrangThai = true;
                    else
                        detai.TrangThai = false;

                    Listdetai.Add(detai);
                }
                ketnoi.Cmb().Close();
                return Listdetai;
            }
            catch (Exception ex)
            {
                return Listdetai;
            }
        }
        public static List<DTO_DeTai> selectDeTai_TheoMaCN(string macn,string maloaidt)
        {
            List<DTO_DeTai> Listdetai = new List<DTO_DeTai>();
            try
            {
                string s = "proc_SelectDetai_TheoLoaiChuyenNganh";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = ketnoi.Cmb();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = s;
                cmd.Parameters.Add("@MaLoaiCN", SqlDbType.NVarChar).Value = macn;
                cmd.Parameters.Add("@LoaiDT", SqlDbType.NVarChar).Value = maloaidt;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DTO_DeTai detai = new DTO_DeTai();
                    detai.MaDT = dt.Rows[i]["MaDT"].ToString();
                    detai.TenDT = dt.Rows[i]["TenDT"].ToString();
                    detai.SVToiDa = int.Parse(dt.Rows[i]["SVToiDa"].ToString());
                    detai.SoSVHH = int.Parse(dt.Rows[i]["SoSVHH"].ToString());
                    detai.MaLoaiDT = dt.Rows[i]["MaLoaiDT"].ToString();
                    detai.MaCN = dt.Rows[i]["MaCN"].ToString();
                    detai.NgayCapNhat = dt.Rows[i]["NgayCapNhat"].ToString();
                    detai.SoTuan = dt.Rows[i]["SoTuan"].ToString();
                    detai.HocKy = dt.Rows[i]["HocKy"].ToString();
                    detai.NamHoc = dt.Rows[i]["NamHoc"].ToString();
                    if ((bool)dt.Rows[i]["TrangThai"] == true)
                        detai.TrangThai = true;
                    else
                        detai.TrangThai = false;

                    Listdetai.Add(detai);
                }
                ketnoi.Cmb().Close();
                return Listdetai;
            }
            catch (Exception ex)
            {
                return Listdetai;
            }
        }
        public static bool proc_Update_SVHH(string Madt,int svhh)
        {
            try
            {
                SqlConnection cn = ketnoi.Cmb();
                string s = "Updata_FullSoSVHH_Detai";
                SqlCommand cmd1 = new SqlCommand(s, cn);
                cmd1.CommandType = CommandType.StoredProcedure;
                cmd1.Parameters.Add("@MaDT", SqlDbType.NVarChar).Value = Madt;
                cmd1.Parameters.Add("@SVHH", SqlDbType.NVarChar).Value = svhh;
                if (cn.State == ConnectionState.Closed)
                    cn.Open();
                cmd1.ExecuteNonQuery();

                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        public static bool Update_SVHH()
        {
            List<DTO_DeTai> Listdetai = new List<DTO_DeTai>();
            Listdetai = selectDeTai_ALL();
            try
            {
                for (int i = 0; i < Listdetai.Count; i++)
                {
                    string madt = Listdetai[i].MaDT;
                    int svhh = DAO_PhanCongDT.getCount_SinhVienHienHanh_PCDT(madt);
                    if (!DAO_DeTai.proc_Update_SVHH(madt, svhh))
                        return false;
                }
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
         public static int TongSVHH_TheoMaCN(string macn)
        {
            try
            {
                string s = "Proc_TongSVHH_TheoMaCN";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = ketnoi.Cmb();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = s;
                cmd.Parameters.Add("@MaCN", SqlDbType.NVarChar).Value = macn;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                int chuyennganh = int.Parse( dt.Rows[0]["SoSVHH"].ToString());
                return chuyennganh;
            }
            catch (Exception ex)
            {
                return 0;
            }
        }
        public static int TongSVToiDa_TheoMaCN(string macn)
        {
            try
            {
                string s = "Proc_TongSVToiDa_TheoMaCN";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = ketnoi.Cmb();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = s;
                cmd.Parameters.Add("@MaCN", SqlDbType.NVarChar).Value = macn;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                int chuyennganh = int.Parse(dt.Rows[0]["SVToiDa"].ToString());
                return chuyennganh;
            }
            catch (Exception ex)
            {
                return 0;
            }
        }
        public static List<DTO_DeTai> Detai_report_theonam_theohocky(string hocky, int namhoc)
        {
            List<DTO_DeTai> listdt = new List<DTO_DeTai>();
            try
            {
                string s = "Prc_Detai_report_theonam_theohocky";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = ketnoi.Cmb();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = s;
                cmd.Parameters.Add("@HocKy", SqlDbType.NVarChar).Value = hocky;
                cmd.Parameters.Add("@NamHoc", SqlDbType.NVarChar).Value = namhoc;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DTO_DeTai detai = new DTO_DeTai();

                    detai.ID = int.Parse(dt.Rows[i]["ID"].ToString());
                    detai.MaDT = dt.Rows[i]["MaDT"].ToString();
                    detai.TenDT = dt.Rows[i]["TenDT"].ToString();
                    detai.SVToiDa = int.Parse(dt.Rows[i]["SVToiDa"].ToString());
                    detai.SoSVHH = int.Parse(dt.Rows[i]["SoSVHH"].ToString());
                    detai.TenloaiDT = dt.Rows[i]["TenloaiDT"].ToString();
                    detai.TenCN = dt.Rows[i]["TenCN"].ToString();
                    detai.TenGV = dt.Rows[i]["TenGV"].ToString();
                    detai.MaLoaiDT = dt.Rows[i]["MaLoaiDT"].ToString();
                    detai.NgayCapNhat = dt.Rows[i]["NgayCapNhat"].ToString();
                    detai.SoTuan = dt.Rows[i]["SoTuan"].ToString();
                    detai.HocKy = dt.Rows[i]["HocKy"].ToString();
                    detai.NamHoc = dt.Rows[i]["NamHoc"].ToString();

                    listdt.Add(detai);
                }
                ketnoi.Cmb().Close();
                return listdt;
            }
            catch (Exception ex)
            {
                return listdt;
            }
        }

        public static List<DTO_DeTai> Detai_report_theonam( int namhoc)
        {
            List<DTO_DeTai> listdt = new List<DTO_DeTai>();
            try
            {
                string s = "Prc_Detai_report_theonam_theohocky";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = ketnoi.Cmb();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = s;
                
                cmd.Parameters.Add("@NamHoc", SqlDbType.NVarChar).Value = namhoc;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DTO_DeTai detai = new DTO_DeTai();

                    detai.ID = int.Parse(dt.Rows[i]["ID"].ToString());
                    detai.MaDT = dt.Rows[i]["MaDT"].ToString();
                    detai.TenDT = dt.Rows[i]["TenDT"].ToString();
                    detai.SVToiDa = int.Parse(dt.Rows[i]["SVToiDa"].ToString());
                    detai.SoSVHH = int.Parse(dt.Rows[i]["SoSVHH"].ToString());
                    detai.TenloaiDT = dt.Rows[i]["TenloaiDT"].ToString();
                    detai.TenCN = dt.Rows[i]["TenCN"].ToString();
                    detai.TenGV = dt.Rows[i]["TenGV"].ToString();
                    detai.MaLoaiDT = dt.Rows[i]["MaLoaiDT"].ToString();
                    detai.NgayCapNhat = dt.Rows[i]["NgayCapNhat"].ToString();
                    detai.SoTuan = dt.Rows[i]["SoTuan"].ToString();
                    detai.HocKy = dt.Rows[i]["HocKy"].ToString();
                    detai.NamHoc = dt.Rows[i]["NamHoc"].ToString();

                    listdt.Add(detai);
                }
                ketnoi.Cmb().Close();
                return listdt;
            }
            catch (Exception ex)
            {
                return listdt;
            }
        }
    }
}